/*******************************************************************************
Project:		Wealth -- Smith, Zidar, and Zwick
Last modified: 	2021-01-26
Modified by:	Dustin Swonder
Description:	This file builds an average deposit rates series from Drechsler, 
				Savov, and Schnabl QJE which excludes checking deposits from the 
				average.
*******************************************************************************/

/*******************************************************************************
	(0) Preliminaries: set filepath for output
*******************************************************************************/

capture mkdir ~/Dropbox/ra_dustin/wealth/figdump/savovrates_excl_checking20210126
cd ~/Dropbox/ra_dustin/wealth/figdump/savovrates_excl_checking20210126

/*******************************************************************************
	(1) Read in figure 1A data series and clean up a bit
*******************************************************************************/

import excel using $rawdir/19_10_26_deposit_rates_from_Alexi_Savov/DSS_Data_Fig1_PanelA_update_2017.xlsx, /// 
	sheet(updates_avg) cellrange(A1:L129) firstrow clear

drop J K

gen year = yofd(quarter)

assert missing(DepRate) if year == 1986
drop if year == 1986 
assert !missing(DepRate)

assert missing(timedepl250k) if year < 2017
replace timedepl250k = 0 if year < 2017
assert !missing(timedepl250k)

assert missing(smalltime100K) if year == 2017
replace smalltime100K = 0 if year == 2017
assert !missing(smalltime100K)

assert missing(smalltime250K) if year != 2017
replace smalltime250K = 0 if year != 2017
assert !missing(smalltime250K)

/*******************************************************************************
	(2) Replicate main deposit rates series
*******************************************************************************/

gen totdeposits = savdep + timedepl100k + timedepl250k + transdep

foreach component of varlist savdep timedepl100k timedepl250k transdep {
	gen `component'_sh_deposits = `component' / totdeposits
}

#delimit ;
gen DepRate_check = (savings * savdep_sh_deposits) + (checking * transdep_sh_deposits) + 
					(smalltime100K * timedepl100k_sh_deposits) + 
					(smalltime250K * timedepl250k_sh_deposits);
#delimit cr
assert abs(DepRate - DepRate_check) < 0.000001
drop DepRate_check

/*******************************************************************************
	(3) Now do the same exercise, excluding checking accounts; then collapse to 
		yield averages by year
*******************************************************************************/

gen totdeposits_excl_checking = savdep + timedepl100k + timedepl250k

foreach component of varlist savdep timedepl100k timedepl250k {
	gen `component'_sh_nonchcking_dep = `component' / totdeposits_excl_checking
}

#delimit ;
gen deprate_excl_checking = (savings * savdep_sh_nonchcking_dep) + 
							(smalltime100K * timedepl100k_sh_nonchcking_dep) + 
							(smalltime250K * timedepl250k_sh_nonchcking_dep);
#delimit cr

rename DepRate deprate_orig

/*******************************************************************************
	(4) Plot non-checking interest rate alongside main rate series
*******************************************************************************/

sort year 
#delimit ;
graph twoway (connected deprate_orig quarter, lcolor(dknavy) msym(none) lpattern(1))
	(connected deprate_excl_checking quarter, lcolor(ebblue) msym(none) lpattern(-)),
	$gpr title("Drechsler-Savov-Schnabl average deposit rates (%)", 
				color(black) size(medsmall) position(11) ring(1))
	ytitle("") xtitle("") // xlab(1990(5)2015) xscale(range(1987 2017)) 
	legend(order(1 "Including checking" 2 "Excluding checking") region(lcolor(white)));
#delimit cr
graph export compare_rates.pdf, replace

/*******************************************************************************
	(5) Export deposit rates series
*******************************************************************************/

cd $rawdir/19_10_26_deposit_rates_from_Alexi_Savov

replace deprate_excl_checking = deprate_excl_checking / 100 // Scale down so between 0 and 1

export delimited year quarter deprate_excl_checking using deprate_excl_checking.csv, replace

capture rm README.txt

!echo "deprate_excl_checking.csv created $S_DATE by xpds_build_savov_rates_excl_checking.do (syzzle repo)." >> README.txt
